<?php

class M_scholarships Extends MY_Model
{
	public function get_scholarships_available_for_stud($id,$sy=FALSE)
	{
		$sql = "SELECT ss_id,
					   ss_name,
					   ss_desc,
					   ss_tuit_percentage,
					   ss_misc_percentage,
					   ss_tuit_cash,
					   ss_misc_cash
				FROM student_scholarships
				WHERE ss_id NOT IN (SELECT scholarship_id 
									FROM student_scholarship_record
								    WHERE stud_e_id = ?
									AND academic_year_id = ?
								   )
			   ";
		if($sy){
			$query = $this->db->query($sql,array($id,$sy));
		}else{
			$query = $this->db->query($sql,array($id,$this->c_sy->id));
		}
		
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	
	public function get_selected_scholarship_and_insert_to_deductions($data)
	{
		$sql = "SELECT ss_id,ss_name,ss_desc,ss_rate
				FROM student_scholarships
				WHERE ss_id = ? ";
		$query = $this->db->query($sql,array($data['scholarships']));
		
		if($query->num_rows() >= 1)
		{
			$result = $query->row();
			
			$input['sdr_remarks'] = $data['remarks'];
			$input['sdr_amount'] = $result->ss_rate;
			$input['sdr_deduction_name'] = $result->ss_name;
			$input['sdr_enrollment_id'] = $data['enrollment_id'];
			$input['sdr_scholarship_id'] = $result->ss_id;
			$input['sdr_schoolyear_id'] = $this->c_sy->id;
			$input['sdr_user_trans_id'] = $this->c_user->userid;
			$input['sdr_created'] = NOW;
			
			$this->db->insert('student_deductions_record',$input);
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		}else{
			return FALSE;
		}
	}
	
	public function get_student_deductions($id,$sy_id=FALSE)
	{
		$sql = "SELECT sdr_amount,
					   sdr_remarks,
					   sdr_id
				FROM student_deductions_record
				WHERE sdr_enrollment_id = ?
				AND sdr_schoolyear_id = ?
				";
		if($sy_id){
			$query = $this->db->query($sql,array($id,$sy_id));
		}else{
			$query = $this->db->query($sql,array($id,$this->c_sy->id));
		}
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}

	public function get_student_scholarships_deducted($e_id,$sy_id=FALSE)
	{
		$sql = "SELECT total_value_deducted,scholarship_name,id as sch_id
				FROM student_scholarship_record
				WHERE stud_e_id = ?
				AND academic_year_id = ?";
		if($sy_id)
		{
			$query = $this->db->query($sql,array($e_id,$sy_id));
		}else{
			$query = $this->db->query($sql,array($e_id,$this->c_sy->id));
		}
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function sum_all_student_deductions($id,$sy_id=FALSE)
	{
		$sql = "SELECT sum(sdr_amount) as total_deductions
				FROM student_deductions_record
				WHERE sdr_enrollment_id = ?
				AND sdr_schoolyear_id = ? ";
		if($sy_id)
		{
			$query = $this->db->query($sql,array($id,$sy_id));
		}else{
			$query = $this->db->query($sql,array($id,$this->c_sy->id));
		}
		
		$sql2 = "SELECT sum(total_value_deducted) as total_deductions
				 FROM student_scholarship_record
				 WHERE stud_e_id = ?
				 AND academic_year_id = ?";
		if($sy_id)
		{
			$query2 = $this->db->query($sql2,array($id,$sy_id));
		}else{
			$query2 = $this->db->query($sql2,array($id,$this->c_sy->id));
		}
		
		
		
		$total_deductions = $query->num_rows() >= 1 ? $query->row()->total_deductions : 0 ;
		$total_scholarship = $query2->num_rows() >= 1 ? $query2->row()->total_deductions : 0 ;
		
		return $total_deductions + $total_scholarship;
	}
	
}